var pool = require("../conf/db").pool;
var logger = require('../conf/log4').logger('dao');

function saveRank(data) {
    var sql = "insert into t_az"+data.source+"_pro_ranking(fetch_time,asin,page_num,page_set,category_name,keyword,";
    sql += "img_url,rank,pro_price,pro_grade,comment_total_number,pro_name,is_promotion_price,brand_name)";
    sql += "values(CURDATE(),?,?,?,?,? ,?,?,?,?,?,?,?,?)";
    pool.getConnection(function (err, connection) {
        if(err){
            logger.error(err);
        } else{
            connection.query(sql,
                [data.asin, data.page, data.index, data.category, data.keyword, data.img, data.rank, data.price, data.grade || 0,
                    data.comm || 0, data.title, data.is_pro_price, data.brand],
                function (err, rows) {
                    if (err) {
                        logger.error(data);
                        logger.error(err);
                    }
                    connection.release();
                });
        }
    });
};


function saveHotkeyRank(data) {
    var sql = "insert into t_az"+data.source+"_hot_key_ranking(fetch_time,asin,page_num,page_set,category_name,keyword,";
    sql += "img_url,rank,pro_price,pro_grade,comment_total_number,pro_name,is_promotion_price,brand_name)";
    sql += "values(?,?,?,?,?,? ,?,?,?,?,?,?,?,?)";
    pool.getConnection(function (err, connection) {
        if(err){
            logger.error(err);
        } else{
            connection.query(sql,
                [data.fetchTime,data.asin, data.page, data.index, data.category, data.keyword, data.img, data.rank, data.price||'', data.grade || 0,
                    data.comm || 0, data.title, data.is_pro_price, data.brand],
                function (err, rows) {
                    if (err) {
                        logger.error(data);
                        logger.error(err);
                    }
                    connection.release();
                });
        }
    });
};


function saveAbsrRank(data) {
    var sql = "insert into t_az"+data.source+"_absr_ranking(fetch_time,asin,pro_price,rank,category_name,pro_url,pro_grade," +
        "comment_total_number)VALUES(CURDATE(),?,?,?,?,?,?,?)";
    pool.getConnection(function (err, connection) {
        if(err) {
            logger.error(err);
        }else{
            connection.query(sql,
                [data.asin, data.price, data.rank, data.category, data.pro_url, data.grade || 0, data.comm || 0],
                function (err, rows) {
                    if (err) {
                        logger.error(data);
                        logger.error(err);
                    }
                    connection.release();
                });
        }
    });
};


function saveAbsrInfo(data) {
    var sql = "insert into t_az"+data.source+"_absr_info(fetch_time,asin,absr_rank,absr_url,category_path)values(CURDATE(),?,?,?,?)" ;
    pool.getConnection(function (err, connection) {
        if(err){
            logger.error(err);
        } else{
            connection.query(sql,
                [data.asin, data.absr_rank, data.absr_url, data.category_path],
                function (err, rows) {
                    if (err) {
                        logger.error(data);
                        logger.error(err);
                    }
                    connection.release();
                });
        }

    });
};

function savePro(data) {
    var sql = "insert into t_az"+data.source+"_pro(fetch_time,asin,pro_name,pro_price,brand_name,pro_img_urls,category_path,absr_info,description,pro_grade,comment_total_number," +
        "comment_level_five,comment_level_four,comment_level_three,comment_level_two,comment_level_one)values(CURDATE(),?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?)";
    pool.getConnection(function (err, connection) {
        if(err) {
            logger.error(err);
        }else {
            connection.query(sql,
                [data.asin, data.pro_name, data.price, data.brand, data.imgs, data.category, data.absr, data.desc, data.grade || 0, data.comm || 0,
                    data.star_5, data.star_4, data.star_3, data.star_2, data.star_1],
                function (err, rows) {
                    if (err) {
                        logger.error(data);
                        logger.error(err);
                    }
                    connection.release();
                });
        }

    });
}
function saveProMHRL(data) {
    var sql = "insert into t_az"+data.source+"_h_comment_detail(fetch_time,comment_create_date,asin,comment_name,pro_color,cust_url,comment_grade" +
        ",comment_content,title,comment_url,original_date,url_asin,review_id) values(CURDATE(),?,?,?,?,?,?, ?,?,?,?,?,?)";
        pool.getConnection(function (err, connection) {
            if(err){
                logger.error(err);
            } else {
                connection.query(sql,
                    [data.comm_create,data.asin, data.comm_name, data.comm_color, data.cust_url, data.comm_grade||0, data.comment,
                        data.comm_title, data.comm_url, data.comm_date, data.asin, data.reviewId],
                    function (err, rows) {
                        if (err) {
                            logger.error(data);
                            logger.error(err);
                        }
                        connection.release();
                    });
            }


        });
}

function saveProCMCR(data) {
    var sql = "replace into t_az"+data.source+"_r_comment_detail(fetch_time,comment_create_date,asin,comment_name,pro_color,cust_url,comment_grade" +
        ",comment_content,title,comment_url,original_date,url_asin,review_id,comment_page,comment_page_set) values(CURDATE(),?,?,?,?,?,?, ?,?,?,?,?,?,?,?)";
    pool.getConnection(function (err, connection) {
        if(err){
            logger.error(err);
        } else {
            connection.query(sql,
                [data.comm_create,data.asin, data.comm_name, data.comm_color, data.cust_url, data.comm_grade, data.comment,
                    data.comm_title, data.comm_url, data.comm_date, data.asin, data.reviewId,data.page,data.page_set],
                function (err, rows) {
                    if (err) {
                        logger.error(data);
                        logger.error(err);
                    }
                    connection.release();
                });
        }

    });
}

function saveBeBound(data) {
    var sql = "insert into t_az"+data.source+"_be_bound_warning(fetch_time,url_asin,bound_color,bound_asin) VALUES(CURDATE(),?,?,?)";
    pool.getConnection(function (err, connection) {
        if(err) {
            logger.error(err);
        } else {
            connection.query(sql,
                [data.asin,data.bound_color, data.bound_asin ],
                function (err, rows) {
                    if (err) {
                        logger.error(data);
                        logger.error(err);
                    }
                    connection.release();
                });
        }

    });
}

function findKeyword(source,foo) {
    var sql = "select category,keyword from t_base_url where source='"+source+"' and (type='TYPE_RANK' or type='TYPE_RANK_2') ";
    pool.getConnection(function (err, connection) {
        if(err){
            logger.error(err);
        } else {
            connection.query(sql, function (err, rows) {
                if (err) {
                    logger.error(err);
                }else {
                    foo(rows);
                }
                connection.release();
            });
        }

    });
};


//
function findHotKey(source,time,foo,jobEp) {
    var sql = "select 'aps' as category,`key` as keyword from t_esr_search_key where source='"+source+"' and isUse=1 and level>1 and fetchTime='"+time+"'  ";
    pool.getConnection(function (err, connection) {
        if(err){
            logger.error(err);
        } else {
            connection.query(sql, function (err, rows) {
                if (err) {
                    logger.error(err);
                }else {
                    foo(rows,jobEp);
                }
                connection.release();
            });
        }

    });
};

function findAbsrCategory(source,foo) {
    var sql = "select url,category from t_base_url where source='"+source+"' and type='TYPE_ABSR_RANK'";
    //console.log(sql);
    pool.getConnection(function (err, connection) {
        if(err) {
            logger.error(err);
        } else {
            connection.query(sql, function (err, rows, fields) {
                if (err) {
                    console.log('[query] - :' + err);
                    return;
                }else {
                    foo(rows);
                }
                connection.release();
            });
        }

    });
};

function findProAsin(source,foo,fn) {
    var sql =  "select asin from t_az"+source+"_pro_ranking  a ";
        sql += "left join (select asin from v_esr_pro_asin where source='"+source+"' and is_putaway=1) b using(asin) ";
        sql += "where a.fetch_time=CURDATE() and (a.page_type=0 or a.page_type=1) and a.page_num<=10 group by a.asin";
    //console.log(sql);
    pool.getConnection(function (err, connection) {
        if(err) {
            logger.error(err);
        } else {
            connection.query(sql, function (err, rows, fields) {
                if (err) {
                    logger.error(err);

                }else {
                    foo(rows,fn);
                }
                connection.release();
            });
        }

    });
};

function saveHotkeyCMCR(data) {
    var sql = "replace into t_az"+data.source+"_hot_key_comment(fetch_time,comment_create_date,asin,comment_name,pro_color,cust_url,comment_grade" +
        ",comment_content,title,comment_url,original_date,url_asin,review_id,comment_page,comment_page_set) values(?,?,?,?,?,?,?, ?,?,?,?,?,?,?,?)";
    pool.getConnection(function (err, connection) {
        if(err){
            logger.error(err);
        } else {
            connection.query(sql,
                [data.fetchTime,data.comm_create,data.asin, data.comm_name, data.comm_color, data.cust_url, data.comm_grade, data.comment,
                    data.comm_title, data.comm_url, data.comm_date, data.asin, data.reviewId,data.page,data.page_set],
                function (err, rows) {
                    if (err) {
                        logger.error(data);
                        logger.error(err);
                    }
                    connection.release();
                });
        }

    });
}

function findHotKeyAsin(source,time,foo,jobEp) {
    var sql =  "select asin from t_az"+source+"_hot_key_ranking where fetch_time='"+time+"' group by asin";
    pool.getConnection(function (err, connection) {
        if(err) {
            logger.error(err);
        } else {
            connection.query(sql, function (err, rows, fields) {
                if (err) {
                    logger.error(err);

                }else {
                    foo(rows,jobEp);
                }
                connection.release();
            });
        }

    });
};

function saveSearchKey(data) {
    var sql = "insert ignore into t_esr_search_key(`topKey`,`parentKey`,`level`,`fetchTime`,`source`,`key`) values(?,?,?,?,?,?) ";
    pool.getConnection(function (err, connection) {
        if(err){
            logger.error(err);
        } else{
            connection.query(sql,
                [data.topKey, data.key, data.level, data.fetchTime, data.source, data.res],
                function (err, rows) {
                    if (err) {
                        logger.error(data);
                        logger.error(err);
                    }
                    connection.release();
                });

        }
    });
}


function findSearchKey(source,times,fn) {
    var sql = "select topKey,`key` from t_esr_search_key where source=? and level=4 and isUse=3";
    //console.log(sql);
    pool.getConnection(function (err, connection) {
        if(err) {
            logger.error(err);
        } else {
            connection.query(sql,[source,times], function (err, rows, fields) {
                if (err) {
                    logger.error(err);
                }else {
                    fn(rows);
                }
                connection.release();
            });
        }

    });
};

function updateCount(time){
    pool.getConnection(function(err,conn){
        var sql = "replace into t_azus_comment_count(comment_create_date,asin,ct) ";
        sql += "select comment_create_date,a.asin,count(1)  from t_azus_hot_key_ranking a LEFT JOIN t_azus_hot_key_comment b on a.asin=b.asin ";
        sql+="where a.fetch_time='"+time+"' group by comment_create_date,a.asin";
        conn.query(sql,function(err,rows){
            if(err)console.log(err);
            conn.release();
        });
    });

};

/*function test() {
    var sql = "insert into t_test(id,val) values(?,?)";
    var params= [];
    params.push([9,'aa1']);
    params.push([8,'ab2']);
    params.push([5,'ac3']);

    execTrans(sql,params,function (err,info) {
        if(err){
            console.log(err);
        }else {
            console.log(info);
        }

    })
}

test();*/

/**
 * 执行事务，批量提交
 * @param sql       执行的SQL语句
 * @param params    参数数组
 * @param callback  回调方法
 */
function execTrans(sql,params, callback) {
    pool.getConnection(function (e, connection) {
        if (e) {
            if(callback)callback(e, null);
        }else{
            connection.beginTransaction(function (err) {
                if (err) {
                    if(callback)callback(err, null);
                }
                logger.info("开始执行transaction，共执行" + params.length + "条数据");
                var EventProxy = require('eventproxy');
                var job = new EventProxy();


                params.forEach(function (data) {
                    connection.query(sql, data, function (tErr) {
                        if (tErr) {
                            logger.error(data);
                            logger.error("执行失败，ERROR：" + tErr);
                            job.emit("query_err",tErr);
                        }else{
                            job.emit("query_done");
                        }
                    });

                });

                job.after("query_done",params.length,function () {
                    connection.commit(function (err, info) {
                        if (err) {
                            logger.error("事务提交失败，ERROR：" + err);
                            connection.rollback(function () {
                                connection.release();
                                if(callback) callback(err, null);
                            });
                        } else {
                            connection.release();
                            if(callback)callback(null, info);
                        }

                    });
                });

                job.once("query_err",function (err) {
                    connection.rollback(function () {
                        connection.release();
                        if(callback)callback(err, null);
                    });
                })
            });
        }
    });
}




module.exports.saveRank = saveRank;
module.exports.saveAbsrRank = saveAbsrRank;
module.exports.savePro = savePro;
module.exports.saveProMHRL = saveProMHRL;
module.exports.saveProCMCR = saveProCMCR;
module.exports.saveBeBound = saveBeBound;
module.exports.saveAbsrInfo = saveAbsrInfo;
module.exports.saveHotkeyRank = saveHotkeyRank;
module.exports.saveHotkeyCMCR = saveHotkeyCMCR;
module.exports.saveSearchKey = saveSearchKey;


module.exports.findKeyword = findKeyword;
module.exports.findHotKey = findHotKey;
module.exports.findAbsrCategory = findAbsrCategory;
module.exports.findProAsin = findProAsin;
module.exports.findHotKeyAsin = findHotKeyAsin;
module.exports.findSearchKey = findSearchKey;
module.exports.updateCount = updateCount;

module.exports.execTrans = execTrans;

